import pandas as pd
import pymysql
from sshtunnel import SSHTunnelForwarder

def combination(departureCityName,arrivalCityName):
    df1 = pd.read_excel('携程航班处理数据.xlsx')
    # 读取第二个Excel文件import requests
    df2 = pd.read_excel(f'携程航班处理数据-{departureCityName}-{arrivalCityName}.xlsx')
    # 合并两个DataFrame
    combined_df = pd.concat([df1, df2])
    # 保存合并后的DataFrame到新的Excel文件
    combined_df.to_excel('携程航班处理数据.xlsx', index=False)

def processTrain(departureCityName,arrivalCityName,nowDate):
    data = pd.read_excel(f"携程航班数据-{departureCityName}-{arrivalCityName}.xlsx")
    #仅保留直达机票
    data = data.loc[data['arrivalCityName'] == arrivalCityName]
    #去除异常值
    data = data.loc[data['discountRate'] != 0.00001]
    # departureDateTime 和 arrivalDateTime将出发时间转换为小时和分钟
    data['departureMonth'] = pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.month
    data['departureDay'] = pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.day
    data['departureHour'] = pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.hour
    data['departureMinute'] = pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.minute
    data['weekday'] = pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.weekday + 1
    data['preDay'] = ((pd.to_datetime(pd.to_datetime(data["departureDateTime"],format='%Y-%m-%d  %H:%M:%S').dt.date) - pd.to_datetime(nowDate))/pd.Timedelta(1, 'D')).fillna(0).astype(int)
    data.drop(["departureDateTime"], axis = 1, inplace = True)
    data.drop(["arrivalDateTime"], axis = 1, inplace = True)
    data.drop(["aircraftName"], axis=1, inplace=True)
    data.to_excel(f'携程航班处理数据-{departureCityName}-{arrivalCityName}.xlsx',index=False,header=True)

def itineraryClear():
    server = SSHTunnelForwarder(
        ('123.249.93.204', 22),
        ssh_password='cxZS1234',
        ssh_username='root',
        remote_bind_address=('127.0.0.1', 3306)
    )
    server.start()
    connection = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                                 port=server.local_bind_port,
                                 user='hpzs',
                                 passwd='123456',
                                 db='flight')
    try:
        with connection.cursor() as cursor:
            query = "truncate table flight_itinerary;"
            cursor.execute(query)
            connection.commit()
    finally:
        connection.close()
    server.close()

def insertItinerary(departureCityName,arrivalCityName):
    data = pd.read_excel(f"携程航班数据-{departureCityName}-{arrivalCityName}.xlsx")
    # 仅保留直达机票
    data = data.loc[data['arrivalCityName'] == arrivalCityName]
    # 去除异常值
    data = data.loc[data['discountRate'] != 0.00001]
    data['departureDate'] = pd.to_datetime(data["departureDateTime"], format='%Y-%m-%d  %H:%M:%S').dt.date
    data['departureTime'] = pd.to_datetime(data["departureDateTime"], format='%Y-%m-%d  %H:%M:%S').dt.time
    data['arrivalDate'] = pd.to_datetime(data["arrivalDateTime"], format='%Y-%m-%d  %H:%M:%S').dt.date
    data['arrivalTime'] = pd.to_datetime(data["arrivalDateTime"], format='%Y-%m-%d  %H:%M:%S').dt.time
    data.drop(["departureDateTime"], axis = 1, inplace = True)
    data.drop(["arrivalDateTime"], axis = 1, inplace = True)
    data.drop(["duration"], axis=1, inplace=True)
    data.drop(["discountRate"], axis=1, inplace=True)

    server = SSHTunnelForwarder(
        ('123.249.93.204', 22),
        ssh_password='cxZS1234',
        ssh_username='root',
        remote_bind_address=('127.0.0.1', 3306)
    )
    server.start()
    connection = pymysql.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1
                                 port=server.local_bind_port,
                                 user='hpzs',
                                 passwd='123456',
                                 db='flight')
    try:
        with connection.cursor() as cursor:
            insert_query = "INSERT INTO flight_itinerary(flight_no,airline,departure_city,departure_airport,arrival_city,arrival_airport,price,departure_date,departure_time,arrival_date,arrival_time,flight_type) VALUES (%s, %s,%s, %s, %s,%s,%s, %s, %s, %s, %s,%s)"
            insert = list(zip(data['flightNo'].tolist(), data['airlineName'].tolist(),data['departureCityName'].tolist(),data['departureAirportName'].tolist()
                              ,data['arrivalCityName'].tolist(),data['arrivalAirportName'].tolist(),data['price'].tolist(),data['departureDate'].astype(str).tolist()
                              ,data['departureTime'].astype(str).tolist(),data['arrivalDate'].astype(str).tolist(),data['arrivalTime'].astype(str).tolist(),data['aircraftName'].tolist()))
            cursor.executemany(insert_query, insert)
            connection.commit()
    finally:
        connection.close()
    server.close()